/**
 * @author EasonChan
 *
 * Nov 16, 2010
 */
package com.musicgeek.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.musicgeek.biz.util.UserSecurityUtil;
import com.musicgeek.dao.util.ConnectionPool;

public class UserDao {
	private static Connection conn;

	public static boolean validateUser(String name, String password) {
		String sql = "SELECT user_name FROM user WHERE user_name = '" + name
				+ "' AND user_pwd = '"
				+ UserSecurityUtil.encryptPassword(password) + "'";
		boolean validatedUser = false;
		try {
			conn = ConnectionPool.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			validatedUser = rs.next();
			ConnectionPool.releaseConnection(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return validatedUser;
	}

	public static boolean isUserExist(String name) {
		String sql = "SELECT user_name FROM user WHERE user_name='" + name
				+ "'";
		boolean isUserExist = false;
		try {
			conn = ConnectionPool.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			isUserExist = rs.next();
			ConnectionPool.releaseConnection(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return isUserExist;
	}

	public static void addUser(String name, String password, String email,
			int sex, String date, int level) {
		String sql = "INSERT INTO user VALUES(null,?,?,?,?,?)";
		try {
			conn = ConnectionPool.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name); // All user name to lowercase
			pstmt.setString(2, UserSecurityUtil.encryptPassword(password));
			pstmt.setString(3, email);
			pstmt.setInt(4, sex);
			pstmt.setInt(5, level);
			pstmt.execute();
			System.out.println("d");
			ConnectionPool.releaseConnection(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static ArrayList<User> queryUser() {
		String sql = "select * from user";
		ArrayList<User> list = new ArrayList<User>();
		try {
			conn = ConnectionPool.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setName(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setEmail(rs.getString(4));
				user.setSex(rs.getString(5));
				user.setLevel(rs.getInt(6));
				list.add(user);
			}
			ConnectionPool.releaseConnection(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public static void main(String[] args) {
		addUser("eason", "q1", "easonchan000@gmail.com", 1, "2010-10-10", 12);
	}
}
